if exists(select name from sysobjects where name = 'sp_KhuyenMaiGetAllByForeignKey')
	drop proc sp_KhuyenMaiGetAllByForeignKey
go
/*-- =============================================
	Created by: HP
	Created on: 2014-04-12 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_KhuyenMaiGetAllByForeignKey.sql
*/-- =============================================

CREATE PROCEDURE sp_KhuyenMaiGetAllByForeignKey
    @Id INT,
    @PageIndex INT = 1,
	@PageSize INT = 10,
	@Type tinyint,
	@RecordCount INT=0 OUTPUT,
	@MyKhuyenMai tinyint
AS
BEGIN
if(@MyKhuyenMai=0)
	begin
		select ROW_NUMBER() over(
			order by a.KhuyenMaiId
		) as RowNumber ,
			a.[KhuyenMaiId],
			a.[TieuDe],
			a.[GiaNiemYet],
			a.[GiaKM],
			a.[ThoiGian],
			a.[Link],
			a.[ImageSmall],
			a.[SiteNguon],
			a.[Provider],
			a.[Summary],
			a.[ThuTu],
			a.[TrangThai],
			a.[NguoiTao],
			a.[NgayTao],
			a.[NguoiSua],
			a.[NgaySua],
			a.[WebId]
		into #Results
		FROM
			KhuyenMai a inner join LoaiKhuyenMaiAndKhuyenMai b on a.KhuyenMaiId=b.KhuyenMaiId WHERE a.TrangThai=@Type and b.LoaiKhuyenMaiId=@Id
			
			set @RecordCount=(select COUNT(KhuyenMaiId) from #Results)
			select * from #Results  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
			drop table #Results
	end
else
	begin
		select ROW_NUMBER() over(
		order by a.KhuyenMaiId
	) as RowNumber ,
		a.[KhuyenMaiId],
		a.[TieuDe],
		a.[GiaNiemYet],
		a.[GiaKM],
		a.[ThoiGian],
		a.[Link],
		a.[ImageSmall],
		a.[SiteNguon],
		a.[Provider],
		a.[Summary],
		a.[ThuTu],
		a.[TrangThai],
		a.[NguoiTao],
		a.[NgayTao],
		a.[NguoiSua],
		a.[NgaySua],
		a.[WebId]
    into #Results2
	FROM
        KhuyenMai a inner join LoaiKhuyenMaiAndKhuyenMai b on a.KhuyenMaiId=b.KhuyenMaiId WHERE a.MyKhuyenMai=@MyKhuyenMai and a.TrangThai=@Type and b.LoaiKhuyenMaiId=@Id
        set @RecordCount=(select COUNT(KhuyenMaiId) from #Results2)
			select * from #Results2  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
			drop table #Results2
	end

END
GO
